// .........................................................................
// Title: relationships.do
//
// Implements analysis of the role of existing relationships in new 
// corporate bond market financing
// .........................................................................

* ---------------------------------------
* Construct insurer relationships panel
* ---------------------------------------

* Sample of individual new IG issues
use "$tmp/mergent_issues_cleaned", clear
mmerge cusip using "$tmp/cusip_to_up_firm_id", unmatched(m)
drop if missing(firm_id)
drop _merge
replace issuance_date = effective_date if missing(issuance_date)
replace issuance_date = offering_date if missing(issuance_date)
count if missing(issuance_date)
gen year = year(issuance_date)
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m)
keep if _merge == 3
keep if inlist(rating_cat, "AAA", "AA", "A", "BBB")
drop _merge
drop if missing(offering_amt)
save "$tmp/us_mergent_new_ig_issues", replace

* Yearly summary for insurer corporate bond holdings
use "$tmp/insurance_corporate_bond_holdings", clear
cap drop year
gen year = year(dofq(date_q))
gcollapse (max) marked_par_value  carrying_value  conditional_fair_value  aggregate_par_value  marketvalue_usd  ///
    clean_marketvalue  actual_cost  shares_owned  value_outstanding  value_outstanding_mv, ///
    by(holder_name holder_key cusip year)
save "$tmp/insurance_corporate_bond_holdings_y_summary", replace

* Find insurers holdings these positions
use "$tmp/insurance_corporate_bond_holdings_y_summary", clear
mmerge cusip year using "$tmp/us_mergent_new_ig_issues", unmatched(m) ukeep(offering_amt)
gen issuance_buyer = 1 if _merge == 3
replace issuance_buyer = 0 if _merge != 3
assert ~missing(issuance_buyer)
drop _merge
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation.dta", unmatched(m)
keep if _merge == 3
drop _merge *source*

* Construct relationship dummy based on same-firm holdings
gsort holder_name year cusip6_up_bg
by holder_name year cusip6_up_bg: egen relationship = min(issuance_buyer)
replace relationship = 1 - relationship
keep if issuance_buyer == 1
save "$tmp/insurer_issuance_buyer_relationships_pos", replace

* Expand the panel to also include negatives
use "$tmp/insurer_issuance_buyer_relationships_pos", clear
keep holder_name holder_key cusip year issuance_buyer relationship
drop if missing(holder_name)
mmerge holder_name using "$tmp/insurer_avg_rank.dta", unmatched(m)
drop _merge holder_key insurer_avg_size
fillin holder_name cusip
replace issuance_buyer = 0 if _fillin == 1
gsort cusip year
by cusip: carryforward year, replace
assert ~missing(year)
mmerge holder_name using "$tmp/insurer_avg_rank.dta", unmatched(m) update
drop _merge insurer_avg_size
assert ~missing(insurer_avg_rank)
drop _fillin
gsort holder_name cusip
save "$tmp/insurer_issuance_buyer_relationships_pre", replace

* Summary of insurer holdings of each firm
use "$tmp/insurance_corporate_bond_holdings_y_summary", clear
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation", unmatched(m)
keep if _merge == 3
drop _merge *source*
gcollapse (sum) marketvalue_usd, by(holder_name year cusip6_up_bg)
save "$tmp/insurance_bg_y_totals", replace

* Construct relationship indicators for negative entries
use "$tmp/insurer_issuance_buyer_relationships_pre", clear
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation", unmatched(m)
keep if _merge == 3
drop _merge *source*
mmerge holder_name year cusip6_up_bg using "$tmp/insurance_bg_y_totals", unmatched(m) uname(u_)
replace relationship = 1 if missing(relationship) & _merge == 3
replace relationship = 0 if missing(relationship) & _merge != 3
drop _merge u_*
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m)
mmerge cusip year using "$tmp/us_mergent_new_ig_issues", unmatched(m) ukeep(offering_amt)
mmerge holder_name using "$tmp/insurer_avg_rank.dta", unmatched(m)
drop _merge
save "$tmp/insurer_issuance_buyer_relationships", replace

* Extend insurer relationship dataframe with yield spreads
use "$tmp/insurer_issuance_buyer_relationships", clear
mmerge cusip using "$raw/cmns/gcap_security_master_cusip", unmatched(m) ukeep(issuance_date)
drop _merge
gen date_m = mofd(issuance_date)
format %tm date_m
mmerge cusip date_m using "$tmp/trace_merged_panel_complete_m", unmatched(m) ukeep(trace_yield_spread_w)
keep if _merge == 3
drop _merge
save "$tmp/insurer_issuance_buyer_relationships_with_spreads", replace

* Construct spread quantiles
use "$tmp/insurer_issuance_buyer_relationships_with_spreads", clear
gsort cusip6_up_bg date_m
by cusip6_up_bg: egen firm_avg_yield_spread = mean(trace_yield_spread_w)
egen spread_decile = xtile(trace_yield_spread_w), nq(10)
save "$tmp/insurer_issuance_buyer_relationships_with_spread_quantiles", replace

* ---------------------------------------
* Estimate relationship effects
* ---------------------------------------

* Run regressions with yield spread quantile interactions
use "$tmp/insurer_issuance_buyer_relationships_with_spread_quantiles", clear
reghdfe issuance_buyer c.relationship##i.spread_decile, absorb(cusip6_up_bg date_m) cluster(cusip6_up_bg)

* Collect the results
gen _spread_decile = .
gen _beta = .
gen _sderr = .
gen _alpha = .
gen _alpha_se = .
gen _gamma = .
gen _gamma_se = .

forval i=2/10 {
    local beta = _b[`i'.spread_decile#c.relationship]
    local sderr = _se[`i'.spread_decile#c.relationship]
    replace _beta = `beta' if _n == `i'
    replace _sderr = `sderr' if _n == `i'
    replace _spread_decile = `i' if _n == `i'
}

local alpha = _b[_cons]
local alpha_se = _se[_cons]
local gamma = _b[relationship]
local gamma_se = _se[relationship]
replace _alpha = `alpha'
replace _alpha_se = `alpha_se'
replace _gamma = `gamma'
replace _gamma_se = `gamma_se'

keep _*
keep if ~missing(_spread_decile)
rename _* *

* Confidence intervals
gen beta_min = beta - 1.96 * sderr
gen beta_max = beta + 1.96 * sderr
gen alpha_min = alpha - 1.96 * alpha_se
gen alpha_max = alpha + 1.96 * alpha_se
gen gamma_min = gamma - 1.96 * gamma_se
gen gamma_max = gamma + 1.96 * gamma_se

* Plot the estimates (Figure 12)
twoway rarea gamma_min gamma_max spread_decile, fcolor(gray%20) lcolor(gray%20) || ///
    scatter beta spread_decile, mcolor(red) fcolor(red) || rcap beta_min beta_max spread_decile, lcolor(red) ylab(0(.02).1) ///
    ytitle("Relationship Effect ({&beta})") legend(off) xtitle(" " "Firm Yield Spread Decile") xlab(1.5 " " 2(1)10 10.5 " ") ///
    yline(`gamma', lcolor(blue) lpattern(solid)) text(.05 1.7 "{&gamma}", color(blue)) ///
    graphregion(margin(1 10 1 1)) xsize(7)

graph export "$graphs/relationships.pdf", as(pdf) replace
